package controller;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.lang.reflect.InvocationTargetException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.swing.JDialog;

import org.apache.commons.beanutils.BeanUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

import util.HibernateUtil;
import view.AddBookPage;
import view.BorrowBookPage;
import view.BorrowReturnBookPage;
import view.EditBookPage;
import view.LendBookPage;
import view.ReturnBookPage;
import view.SearchBookPage;
import view.ViewBookPage;
import bean.Book;
import bean.Catalog;
import bean.Loan;
import bean.User;

import com.mysql.jdbc.StringUtils;

import dto.BookDTO;
import dto.BorrowBookDTO;
import dto.LendBookDTO;
import dto.LogonDTO;
import dto.ReturnBookDTO;
import dto.SearchBookDTO;
import dto.UserLoanBookDTO;

public class BookController {

	private AddBookPage addBook;
	private SearchBookPage searchBook;
	private EditBookPage editBook;
	private ViewBookPage viewBook;
	private BorrowReturnBookPage borrowReturnBook;
	private BorrowBookPage borrowBook;
	private LendBookPage lendBook;
	private ReturnBookPage returnBook;

	private BookDTO dto;
	private SearchBookDTO searchDTO;
	private BorrowBookDTO borrowDTO;
	private ReturnBookDTO returnDTO;

	private String userName;
	private String errorMessage;

	void addBook(LogonDTO logonDTO) {
		userName = logonDTO.getUserName();

		addBook = new AddBookPage(logonDTO);

		addBook.addAddBookButtonListener(new AddBookListener());
		addBook.addGetISBNInfoButtonListener(new GetISBNInfoListener("ADD"));

		addBook.setModal(true);
		addBook.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
		addBook.setVisible(true);

	}

	void searchForBook(LogonDTO logonDTO, String mode) {

		searchBook = new SearchBookPage(logonDTO, mode);

		searchBook.addSearchBookButtonListener(new SearchBookListener(mode));

		searchBook.setModal(true);
		searchBook.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
		searchBook.setVisible(true);

	}

	void editBook(LogonDTO logonDTO, BookDTO bookDTO) {
		userName = logonDTO.getUserName();

		editBook = new EditBookPage(logonDTO, bookDTO);

		editBook.addEditBookButtonListener(new EditBookListener());
		editBook.addGetISBNInfoButtonListener(new GetISBNInfoListener("EDIT"));

		editBook.setModal(true);
		editBook.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
		editBook.setVisible(true);

	}

	void viewBook(LogonDTO logonDTO, BookDTO bookDTO) {
		userName = logonDTO.getUserName();

		viewBook = new ViewBookPage(logonDTO, bookDTO);

		viewBook.setModal(true);
		viewBook.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
		viewBook.setVisible(true);

	}

	void borrowReturnBook(LogonDTO logonDTO) {
		userName = logonDTO.getUserName();

		borrowReturnBook = new BorrowReturnBookPage(logonDTO);

		borrowReturnBook.addBorrowBookButtonListener(new BorrowBookListener());
		borrowReturnBook.addReturnBookButtonListener(new ReturnBookListener());

		borrowReturnBook.setModal(true);
		borrowReturnBook.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
		borrowReturnBook.setVisible(true);

	}

	void borrowBook(LogonDTO logonDTO, BorrowBookDTO borrowBookDTO) {
		this.borrowDTO = borrowBookDTO;

		borrowBook = new BorrowBookPage(logonDTO, borrowDTO);

		borrowBook.addViewBookButtonListener(new ViewBookListener());
		borrowBook.addLendBookButtonListener(new ActionListener() {

			public void actionPerformed(ActionEvent arg0) {

				borrowDTO = borrowBook.getBorrowBookDTO();
				String ddc = borrowDTO.getBookDTO().getDeweyDecimalNo();
				BookDTO bookDTO = populateBookDTO(ddc);

				if (bookDTO != null) {

					if ("N".equalsIgnoreCase(bookDTO.getOnLoan())) {
						borrowDTO.setBookDTO(bookDTO);
						lendBook(borrowBook.getLogonDTO(), borrowDTO);
					} else {
						borrowBook
								.displayMessage("Book is not available for lending.");
					}

				} else {
					borrowBook
							.displayMessage("Catalog Number (DDC) unrecognized: "
									+ ddc);
				}
			}
		});

		List<Loan> loanList = fetchLoanByUser(borrowDTO.getBorrowerId());
		List<UserLoanBookDTO> userLoanList = populateUserLoanBookDTO(loanList);
		borrowBook.setLoanedBooks(userLoanList);

		borrowBook.reDrawThis(new ActionListener() {
			public void actionPerformed(ActionEvent arg0) {

				int index = Integer.parseInt(arg0.getActionCommand());
				UserLoanBookDTO userLoanBookDTO = borrowBook.getLoanedBooks()
						.get(index);

				viewBook(borrowBook.getLogonDTO(), userLoanBookDTO.getBookDTO());
			}
		});

		borrowBook.setModal(true);
		borrowBook.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
		borrowBook.setVisible(true);

	}

	void lendBook(LogonDTO logonDTO, BorrowBookDTO borrowBookDTO) {
		this.borrowDTO = borrowBookDTO;
		this.userName = logonDTO.getUserName();

		lendBook = new LendBookPage(logonDTO, borrowBookDTO);
		lendBook.addLendBookButtonListener(new LendBookListener());

		lendBook.setModal(true);
		lendBook.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
		lendBook.setVisible(true);

	}

	void returnBook(LogonDTO logonDTO, ReturnBookDTO returnBookDTO) {
		this.returnDTO = returnBookDTO;

		returnBook = new ReturnBookPage(logonDTO, returnBookDTO);

		String returnerId = returnDTO.getReturnerId();
		String ddc = returnDTO.getDeweyDecimalNo();
		String errorMsg = null;

		List<UserLoanBookDTO> userLoanList = new ArrayList<UserLoanBookDTO>();

		if (!StringUtils.isNullOrEmpty(returnerId)) {
			errorMsg = "No borrowed book.";

			List<Loan> loanList = fetchLoanByUser(returnerId);
			userLoanList = populateUserLoanBookDTO(loanList);

		} else if (!StringUtils.isNullOrEmpty(ddc)) {
			List<Loan> loanList = fetchLoanByDDC(ddc);
			userLoanList = populateUserLoanBookDTO(loanList);

		}

		if (userLoanList.size() != 0) {
			returnBook.setLoanedBooks(userLoanList);

			returnBook.reDrawThis(new ActionListener() {
				public void actionPerformed(ActionEvent arg0) {

					int index = Integer.parseInt(arg0.getActionCommand());
					UserLoanBookDTO userLoanBookDTO = returnBook
							.getLoanedBooks().get(index);

					viewBook(returnBook.getLogonDTO(),
							userLoanBookDTO.getBookDTO());
				}
			}, new ReturnABookListener());
		} else {
			returnBook.displayMessage(errorMsg);
		}

		returnBook.setModal(true);
		returnBook.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
		returnBook.setVisible(true);

	}

	class AddBookListener implements ActionListener {

		public void actionPerformed(ActionEvent arg0) {
			dto = addBook.getBookDTO();

			if (checksBookEntry("ADD")) {
				addBookToDB();
				addBook.displayMessage("\"" + dto.getTitle()
						+ "\" has been added.");
				addBook.clearFields();
			} else {
				addBook.displayMessage(errorMessage);
			}
		}
	}

	class GetISBNInfoListener implements ActionListener {
		private String addOrEdit;

		GetISBNInfoListener(String addOrEdit) {
			this.addOrEdit = addOrEdit;
		}

		public void actionPerformed(ActionEvent arg0) {
			if (addOrEdit == "ADD") {
				dto = addBook.getBookDTO();
				addBook.displayMessage("         ");
			} else if (addOrEdit == "EDIT") {
				dto = editBook.getBookDTO();
				editBook.displayMessage("         ");
			}

			String ISBN = dto.getISBN();

			if (ISBN == null) {
				if (addOrEdit == "ADD")
					addBook.displayMessage("ISBN field should not be empty.");
				else if (addOrEdit == "EDIT")
					editBook.displayMessage("ISBN field should not be empty.");

			} else {
				Book book = fetchBookByISBN(ISBN);

				// no ISBN exists
				if (book != null) {

					try {
						BeanUtils.copyProperties(dto, book);

					} catch (IllegalAccessException iae) {
						iae.printStackTrace();
					} catch (InvocationTargetException ite) {
						ite.printStackTrace();
					}

					if (addOrEdit == "ADD") {
						addBook.setBookDTO(dto);
						addBook.updateWindowData();
					} else if (addOrEdit == "EDIT") {
						editBook.setBookDTO(dto);
						editBook.updateWindowData();
					}
				} else {
					if (addOrEdit == "ADD")
						addBook.displayMessage("No ISBN in record.");
					else if (addOrEdit == "EDIT")
						editBook.displayMessage("No ISBN in record.");
				}
			}
		}
	}

	class SearchBookListener implements ActionListener {

		private String mode;

		SearchBookListener(String mode) {
			this.mode = mode;
		}

		public void actionPerformed(ActionEvent arg0) {
			searchDTO = searchBook.getSearchBookDTO();
			List<BookDTO> books = new ArrayList<BookDTO>();

			// no checkbox is selected
			if (!searchDTO.isCheckedTitle() && !searchDTO.isCheckedAuthor()
					&& !searchDTO.isCheckedCatalogNo()
					&& !searchDTO.isCheckedISBN()) {
				searchBook.displayMessage("No search criteria selected.");
			} else {

				List<Catalog> searchResults = searchDatabase();

				for (Catalog catalog : searchResults) {

					BookDTO bookDTO = new BookDTO();

					try {
						BeanUtils.copyProperties(bookDTO, catalog.getBook());
						bookDTO.setDeweyDecimalNo(catalog.getDeweyDecimalNo());
						bookDTO.setOnLoan(catalog.getOnLoan());
						bookDTO.setRemarks(catalog.getRemarks());

					} catch (IllegalAccessException e) {
						e.printStackTrace();
					} catch (InvocationTargetException e) {
						e.printStackTrace();
					}

					books.add(bookDTO);
				}

				searchBook.setSearchedBooks(books);

				searchBook.reDrawThis(new ActionListener() {

					public void actionPerformed(ActionEvent arg0) {
						int index = Integer.parseInt(arg0.getActionCommand());

						if (mode == "EDIT")
							editBook(searchBook.getLogonDTO(), searchBook
									.getSearchedBooks().get(index));
						else
							viewBook(searchBook.getLogonDTO(), searchBook
									.getSearchedBooks().get(index));
					}
				}, new DeleteBookListener());

				searchBook.displayMessage("Found " + books.size()
						+ " match(es).");
			}
		}
	}

	class EditBookListener implements ActionListener {

		public void actionPerformed(ActionEvent arg0) {
			dto = editBook.getBookDTO();

			if (checksBookEntry("EDIT")) {
				editBookInDB();
				editBook.displayMessage("\"" + dto.getTitle()
						+ "\" has been updated.");
			} else {
				editBook.displayMessage(errorMessage);
			}
		}
	}

	class DeleteBookListener implements ActionListener {

		public void actionPerformed(ActionEvent arg0) {
			int index = Integer.parseInt(arg0.getActionCommand());
			BookDTO selectedToDeleteBook = searchBook.getSearchedBooks().get(
					index);

			String bookCatalog = "[" + selectedToDeleteBook.getDeweyDecimalNo()
					+ "]" + selectedToDeleteBook.getTitle();
			int choice = searchBook.showConfirmDialog(bookCatalog);

			if (choice == 0) {
				System.out.println("yes delete");
				deleteBookInDB(selectedToDeleteBook);

				searchBook.getSearchedBooks().remove(selectedToDeleteBook);

				searchBook.reDrawThis(new ActionListener() {

					public void actionPerformed(ActionEvent arg0) {
						int index = Integer.parseInt(arg0.getActionCommand());
						editBook(searchBook.getLogonDTO(), searchBook
								.getSearchedBooks().get(index));
					}
				}, new DeleteBookListener());
				searchBook.displayMessage(bookCatalog + " deleted.");
			}
		}
	}

	class BorrowBookListener implements ActionListener {

		public void actionPerformed(ActionEvent arg0) {
			LogonDTO logonDTO = borrowReturnBook.getLogonDTO();
			borrowDTO = borrowReturnBook.getBorrowBookDTO();

			if (!StringUtils.isNullOrEmpty(borrowDTO.getBorrowerId())) {
				User user = getMemberInfo(borrowDTO.getBorrowerId());

				if (user != null) {
					borrowDTO.setBorrowerName(user.getUserName());
					borrowBook(logonDTO, borrowDTO);
				} else {
					borrowReturnBook
							.displayBorrowMessage("Invalid borrower id: "
									+ borrowDTO.getBorrowerId());
				}
			} else {
				borrowReturnBook
						.displayBorrowMessage("Input borrower's username");
			}
		}
	}

	class ReturnBookListener implements ActionListener {

		public void actionPerformed(ActionEvent arg0) {
			LogonDTO logonDTO = borrowReturnBook.getLogonDTO();
			returnDTO = borrowReturnBook.getReturnBookDTO();

			String returnerId = returnDTO.getReturnerId();
			String ddc = returnDTO.getDeweyDecimalNo();

			if (!StringUtils.isNullOrEmpty(returnerId)) {

				User user = getMemberInfo(returnDTO.getReturnerId());

				if (user != null) {
					returnDTO.setReturnerName(user.getUserName());
					returnBook(logonDTO, returnDTO);

				} else {
					borrowReturnBook
							.displayReturnMessage("Invalid returner id: "
									+ returnerId);
				}
			} else if (!StringUtils.isNullOrEmpty(ddc)) {

				Catalog catalogBean = fetchCatalogByDDC(ddc);

				if (catalogBean != null) {

					List<Loan> loanList = fetchLoanByDDC(ddc);

					if (loanList.size() != 0) {
						User user = getMemberInfo(loanList.get(0).getUserId());

						returnDTO.setReturnerId(user.getId());
						returnDTO.setReturnerName(user.getUserName());
						returnBook(logonDTO, returnDTO);
						returnBook(logonDTO, returnDTO);

					} else {
						borrowReturnBook
								.displayReturnMessage("The book doesn't seem to be borrowed by any user.");
					}
				} else {
					borrowReturnBook
							.displayReturnMessage("Catalog Number (DDC) unrecognized: "
									+ ddc);
				}
			} else {
				borrowReturnBook
						.displayReturnMessage("Input borrower's username or catalog number");
			}
		}
	}

	class ReturnABookListener implements ActionListener {

		public void actionPerformed(ActionEvent arg0) {

			ReturnBookDTO returnBookDTO = returnBook.getReturnBookDTO();

			int index = Integer.parseInt(arg0.getActionCommand());
			UserLoanBookDTO userLoanBookDTO = returnBook.getLoanedBooks().get(
					index);
			updateReturnBookLoanEntry(userLoanBookDTO,
					returnBookDTO.getReturnerId());

			returnBook.displayMessage("Book succesfully returned.");

			String returnerId = returnBookDTO.getReturnerId();
			String ddc = returnBookDTO.getDeweyDecimalNo();

			List<UserLoanBookDTO> userLoanList = new ArrayList<UserLoanBookDTO>();

			if (!StringUtils.isNullOrEmpty(returnerId)) {

				List<Loan> loanList = fetchLoanByUser(returnerId);
				userLoanList = populateUserLoanBookDTO(loanList);

			} else if (!StringUtils.isNullOrEmpty(ddc)) {

				List<Loan> loanList = fetchLoanByDDC(ddc);
				userLoanList = populateUserLoanBookDTO(loanList);
			}

			if (userLoanList.size() != 0) {
				returnBook.setLoanedBooks(userLoanList);

				returnBook.reDrawThis(new ActionListener() {
					public void actionPerformed(ActionEvent arg0) {

						int index = Integer.parseInt(arg0.getActionCommand());
						UserLoanBookDTO userLoanBookDTO = returnBook
								.getLoanedBooks().get(index);

						viewBook(returnBook.getLogonDTO(),
								userLoanBookDTO.getBookDTO());
					}
				}, new ReturnABookListener());
			}
		}
	}

	class ViewBookListener implements ActionListener {

		public void actionPerformed(ActionEvent e) {
			borrowDTO = borrowBook.getBorrowBookDTO();
			String ddc = borrowDTO.getBookDTO().getDeweyDecimalNo();

			BookDTO bookDTO = populateBookDTO(ddc);

			if (bookDTO != null) {
				viewBook(borrowBook.getLogonDTO(), bookDTO);

			} else {
				borrowBook.displayMessage("Catalog Number (DDC) unrecognized: "
						+ ddc);
			}
		}
	}

	class LendBookListener implements ActionListener {

		public void actionPerformed(ActionEvent e) {
			LendBookDTO lendBookDTO = lendBook.getLendBookDTO();

			addBookLoanEntry(lendBookDTO);

			lendBook.displayMessage(borrowDTO.getBookDTO().getTitle()
					+ " is successfully lent to " + borrowDTO.getBorrowerId());
		}
	}

	private boolean checksBookEntry(String addOrEdit) {

		boolean isValid = true;

		if (StringUtils.isNullOrEmpty(dto.getTitle())) {
			errorMessage = "Title field should not be empty.";
			isValid = false;
		} else if (StringUtils.isNullOrEmpty(dto.getAuthor())) {
			errorMessage = "Author field should not be empty.";
			isValid = false;
		} else if (StringUtils.isNullOrEmpty(dto.getISBN())) {
			errorMessage = "ISBN field should not be empty.";
			isValid = false;
		} else if (StringUtils.isNullOrEmpty(dto.getDeweyDecimalNo())) {
			errorMessage = "Catalog field should not be empty.";
			isValid = false;
		}

		if (addOrEdit == "ADD") {
			if (existsInCatalog()) {
				errorMessage = "Catalog Number(DDC) already exists.";
				isValid = false;
			}
		}

		return isValid;
	}

	private boolean existsInCatalog() {

		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();

		Query query = session.createQuery(" FROM Catalog WHERE DDC = '"
				+ dto.getDeweyDecimalNo() + "'");

		if (!query.list().isEmpty()) {
			return true;
		}

		return false;
	}

	private void addBookToDB() {

		Book bookBean = new Book();
		Catalog catalogBean = new Catalog();

		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();
		Transaction transaction;

		try {

			// already exists in DB, so insert into Catalog only
			Book book = fetchBookByISBN(dto.getISBN());
			if (book != null) {
				catalogBean.setBook(book);
				catalogBean.setOnLoan("N");
				catalogBean.setDeweyDecimalNo(dto.getDeweyDecimalNo());
				catalogBean.setEditedBy(userName);
				catalogBean.setEditedDate(getCurrentTime());

				transaction = session.beginTransaction();
				session.save(catalogBean);
				transaction.commit();
			} else {
				// insert into Book and Catalog

				BeanUtils.copyProperties(bookBean, dto);
				// TODO publishedDate should be inputted by user
				bookBean.setPublishedDate("OCT 1990");
				bookBean.setEditedBy(userName);
				bookBean.setEditedDate(getCurrentTime());

				transaction = session.beginTransaction();
				session.save(bookBean);
				transaction.commit();

				// TODO check add book
				catalogBean.setBook(bookBean);
				catalogBean.setOnLoan("N");
				catalogBean.setDeweyDecimalNo(dto.getDeweyDecimalNo());
				catalogBean.setEditedBy(userName);
				catalogBean.setEditedDate(getCurrentTime());

				transaction = session.beginTransaction();
				session.save(catalogBean);
				transaction.commit();
			}
		} catch (IllegalAccessException iae) {
			iae.printStackTrace();
		} catch (InvocationTargetException ite) {
			ite.printStackTrace();
		}
	}

	private void editBookInDB() {

		Book bookBean = new Book();

		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();
		Transaction transaction;

		// TODO can be put in a common method

		Catalog catalogBean = fetchCatalogByDDC(dto.getDeweyDecimalNo());
		bookBean = catalogBean.getBook();

		try {
			// if ISBN is not edited
			if (dto.getISBN().equals(bookBean.getISBN())) {
				BeanUtils.copyProperties(bookBean, dto);

				bookBean.setEditedBy(userName);
				bookBean.setEditedDate(getCurrentTime());

				transaction = session.beginTransaction();
				session.saveOrUpdate(bookBean);
				transaction.commit();

			} else if (!dto.getISBN().equals(bookBean.getISBN())) {
				// if ISBN is edited

				Book newBook = fetchBookByISBN(dto.getISBN());

				if (newBook != null) {
					// if already existing in DB, update book and catalog

					System.out.println("inside edited ISBN");

					BeanUtils.copyProperties(newBook, dto);
					newBook.setEditedBy(userName);
					newBook.setEditedDate(getCurrentTime());

					transaction = session.beginTransaction();
					session.saveOrUpdate(newBook);
					transaction.commit();

					catalogBean.setBook(newBook);
					catalogBean.setOnLoan("N");
					catalogBean.setEditedBy(userName);
					catalogBean.setEditedDate(getCurrentTime());

					transaction = session.beginTransaction();
					session.saveOrUpdate(catalogBean);
					transaction.commit();

				} else {
					// if not existing in DB, add book in DB, update catalog

					System.out.println("inside else");

					newBook = new Book();

					BeanUtils.copyProperties(newBook, dto);

					// TODO publishedDate should be inputted by user
					newBook.setPublishedDate("OCT 1990");
					newBook.setEditedBy(userName);
					newBook.setEditedDate(getCurrentTime());

					transaction = session.beginTransaction();
					session.save(newBook);
					transaction.commit();

					catalogBean.setBook(newBook);
					catalogBean.setOnLoan("N");
					catalogBean.setEditedBy(userName);
					catalogBean.setEditedDate(getCurrentTime());

					transaction = session.beginTransaction();
					session.saveOrUpdate(catalogBean);
					transaction.commit();
				}
			}
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
	}

	private void deleteBookInDB(BookDTO bookDTO) {
		System.out.println("deleteBookInDB");

		Catalog catalogBean = new Catalog();

		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();
		Transaction transaction;

		Query query = session
				.createQuery(" FROM Catalog WHERE deweyDecimalNo = '"
						+ bookDTO.getDeweyDecimalNo() + "'");

		if (!query.list().isEmpty()) {
			System.out.println("to delete");
			catalogBean = (Catalog) query.list().get(0);

			transaction = session.beginTransaction();
			session.delete(catalogBean);
			transaction.commit();
		}
	}

	private void addBookLoanEntry(LendBookDTO lendBookDTO) {

		// update Catalog.onLoan="Y"
		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();
		Transaction transaction = null;

		Catalog catalogBean = fetchCatalogByDDC(lendBookDTO.getDeweyDecimalNo());
		catalogBean.setOnLoan("Y");
		catalogBean.setEditedBy(userName);
		catalogBean.setEditedDate(getCurrentTime());

		transaction = session.beginTransaction();
		session.saveOrUpdate(catalogBean);
		transaction.commit();

		// insert entry in Loan
		Loan loanBean = new Loan();

		loanBean.setCatalog(catalogBean);
		loanBean.setUserId(lendBookDTO.getBorrowerId());

		// TODO is format correct?
		Date dueDate = lendBookDTO.getDueDate();
		loanBean.setLoanDate(lendBookDTO.getLoanDate());
		loanBean.setDueDate(dueDate);
		loanBean.setIsExtended("N");
		loanBean.setIsReturned("N");
		loanBean.setRemarks(lendBookDTO.getRemarks());
		loanBean.setEditedBy(userName);
		loanBean.setEditedDate(getCurrentTime());

		transaction = session.beginTransaction();
		session.save(loanBean);
		transaction.commit();
	}

	private void updateReturnBookLoanEntry(UserLoanBookDTO userLoanBookDTO,
			String returnerId) {

		BookDTO selectedToReturnBook = userLoanBookDTO.getBookDTO();

		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();
		Transaction transaction = null;

		Catalog catalogBean = fetchCatalogByDDC(selectedToReturnBook
				.getDeweyDecimalNo());

		// update entry in Loan
		Loan loanBean = fetchLoanByPrimaryKeys(catalogBean.getId(), returnerId,
				userLoanBookDTO.getLoanDate());

		loanBean.setIsReturned("Y");
		loanBean.setInDate(getCurrentTime());
		loanBean.setEditedBy(userName);
		loanBean.setEditedDate(getCurrentTime());

		transaction = session.beginTransaction();
		session.saveOrUpdate(loanBean);
		transaction.commit();

		// update Catalog.onLoan="N"
		catalogBean.setOnLoan("N");
		catalogBean.setEditedBy(userName);
		catalogBean.setEditedDate(getCurrentTime());

		transaction = session.beginTransaction();
		session.saveOrUpdate(catalogBean);
		transaction.commit();
	}

	private Book fetchBookByISBN(String ISBN) {

		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();

		Query query = session.createQuery(" FROM Book WHERE ISBN = '" + ISBN
				+ "'");

		Book book = null;
		if (!query.list().isEmpty()) {
			book = (Book) query.list().get(0);
		}

		return book;
	}

	private List<Catalog> searchDatabase() {

		List<Catalog> searchResult = new ArrayList<Catalog>();

		String queryString = null;
		String title = searchDTO.getTitle();
		String author = searchDTO.getAuthor();
		String ddc = searchDTO.getDeweyDecimalNo();
		String ISBN = searchDTO.getISBN();

		if (searchDTO.isCheckedTitle() && searchDTO.isCheckedAuthor()) {
			// search by title and author
			queryString = "SELECT c FROM Catalog c LEFT JOIN FETCH c.book WHERE c.book.title LIKE '%"
					+ title + "%' AND c.book.author LIKE '%" + author + "%'";

		} else if (searchDTO.isCheckedTitle()) {
			// search by title only
			queryString = "SELECT c FROM Catalog c LEFT JOIN FETCH c.book WHERE c.book.title LIKE '%"
					+ title + "%'";

		} else if (searchDTO.isCheckedAuthor()) {
			// search by author only
			queryString = "SELECT c FROM Catalog c LEFT JOIN FETCH c.book WHERE c.book.author LIKE '%"
					+ author + "%'";

		} else if (searchDTO.isCheckedCatalogNo()) {
			// search by catalog only
			queryString = "SELECT c FROM Catalog c LEFT JOIN FETCH c.book WHERE c.deweyDecimalNo = '"
					+ ddc + "'";

		} else if (searchDTO.isCheckedISBN()) {
			// search by ISBN only
			queryString = "SELECT c FROM Catalog c LEFT JOIN FETCH c.book WHERE c.book.ISBN = '"
					+ ISBN + "'";

		}

		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();

		Query query = session.createQuery(queryString);

		List catalogList = query.list();
		if (!catalogList.isEmpty()) {
			for (int i = 0; i < catalogList.size(); i++) {
				Catalog catalog = (Catalog) catalogList.get(i);
				searchResult.add(catalog);
			}
		}

		return searchResult;
	}

	private List<Loan> fetchLoanByUser(String userId) {
		List<Loan> searchResult = new ArrayList<Loan>();

		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();

		Query query = session
				.createQuery("SELECT loan FROM Loan loan LEFT JOIN FETCH loan.catalog WHERE loan.isReturned = 'N' AND loan.userId = '"
						+ userId + "'");

		List loanList = query.list();
		if (!loanList.isEmpty()) {
			for (int i = 0; i < loanList.size(); i++) {
				Loan loan = (Loan) loanList.get(i);
				searchResult.add(loan);
			}
		}

		return searchResult;
	}

	private List<Loan> fetchLoanByDDC(String ddc) {
		List<Loan> searchResult = new ArrayList<Loan>();

		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();

		Query query = session
				.createQuery("SELECT loan FROM Loan loan LEFT JOIN FETCH loan.catalog "
						+ "WHERE loan.isReturned = 'N' AND loan.catalog.deweyDecimalNo = '"
						+ ddc + "'");

		List loanList = query.list();
		if (!loanList.isEmpty()) {
			Loan loan = (Loan) loanList.get(0);
			searchResult.add(loan);
		}

		return searchResult;
	}

	private List<UserLoanBookDTO> populateUserLoanBookDTO(
			List<Loan> searchResult) {

		List<UserLoanBookDTO> userLoanList = new ArrayList<UserLoanBookDTO>();

		for (Loan loan : searchResult) {
			Catalog catalog = loan.getCatalog();
			catalog = fetchCatalogByDDC(catalog.getDeweyDecimalNo());

			UserLoanBookDTO userLoanBookDTO = new UserLoanBookDTO();
			BookDTO bookDTO = new BookDTO();

			try {
				BeanUtils.copyProperties(bookDTO, catalog.getBook());
				bookDTO.setDeweyDecimalNo(catalog.getDeweyDecimalNo());
				bookDTO.setOnLoan(catalog.getOnLoan());
				bookDTO.setRemarks(catalog.getRemarks());

				userLoanBookDTO.setBookDTO(bookDTO);
				userLoanBookDTO.setLoanDate(loan.getLoanDate());
				userLoanBookDTO.setDueDate(loan.getDueDate());

			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				e.printStackTrace();
			}

			userLoanList.add(userLoanBookDTO);
		}

		return userLoanList;
	}

	private User getMemberInfo(String userName) {
		User user = null;

		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();

		Query query = session.createQuery(" FROM User WHERE userId = '"
				+ userName + "'");

		if (!query.list().isEmpty()) {
			user = (User) query.list().get(0);
		}
		return user;
	}

	private Catalog fetchCatalogByDDC(String ddc) {
		Catalog catalogBean = null;

		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();

		Query query = session
				.createQuery("SELECT c FROM Catalog c LEFT JOIN FETCH c.book WHERE c.deweyDecimalNo = '"
						+ ddc + "'");

		if (!query.list().isEmpty()) {
			catalogBean = (Catalog) query.list().get(0);
		}

		return catalogBean;
	}

	private Loan fetchLoanByPrimaryKeys(int catalogId, String userId,
			Date loanDate) {
		Loan loanBean = null;

		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.openSession();

		Query query = session
				.createQuery("SELECT loan FROM Loan loan LEFT JOIN FETCH loan.catalog WHERE loan.catalog.id = "
						+ catalogId
						+ " AND loan.userId = '"
						+ userId
						+ "' AND loan.loanDate = '" + loanDate + "'");

		if (!query.list().isEmpty()) {
			loanBean = (Loan) query.list().get(0);
		}

		return loanBean;
	}

	private BookDTO populateBookDTO(String ddc) {
		BookDTO bookDTO = null;

		Catalog catalogBean = fetchCatalogByDDC(ddc);

		if (catalogBean != null) {
			Book bookBean = catalogBean.getBook();
			bookDTO = new BookDTO();

			try {
				BeanUtils.copyProperties(bookDTO, bookBean);
				bookDTO.setDeweyDecimalNo(catalogBean.getDeweyDecimalNo());
				bookDTO.setOnLoan(catalogBean.getOnLoan());
				bookDTO.setRemarks(catalogBean.getRemarks());

			} catch (IllegalAccessException e1) {
				e1.printStackTrace();
			} catch (InvocationTargetException e1) {
				e1.printStackTrace();
			}
		}

		return bookDTO;
	}

	private Timestamp getCurrentTime() {
		java.util.Date date = new java.util.Date(System.currentTimeMillis());
		java.sql.Timestamp timestamp = new java.sql.Timestamp(date.getTime());

		return timestamp;
	}

}